﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.Objects.DataClasses;
using System.Data.OleDb;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using OpenSoftStore.Orm.Model;
using OpenSoftStore.Orm.Static;

namespace OpenSoftStore.Orm
{
    public class EfToAccess : IDisposable
    {
        #region Constancts

        #endregion

        #region Fields

        private readonly List<object> _mArrAdd = new List<object>();
        private readonly Dictionary<string, List<string>> _mArrDel = new Dictionary<string, List<string>>();
        private readonly Dictionary<string, Dictionary<string, Dictionary<string, object[]>>> _mArrDetection =
            new Dictionary<string, Dictionary<string, Dictionary<string, object[]>>>();
        private readonly List<object> _mArrUpdate = new List<object>();
        public OleDbConnection AccessConn = null;
        public OleDbCommand AccessCommand =null;

        public string MConnString = "";
        public bool MIsDetectionChange = false;
        public string MLastSqlCommand = "";

        #endregion

        #region Caches

        private static Dictionary<string, PropertyInfo[]> _classesDictionary;

        private PropertyInfo[] GetProperties(Type type)
        {
            if (!_classesDictionary.Any(m => m.Key == type.Name))
            {
                PropertyInfo[] temproperties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
                _classesDictionary.Add(type.Name, temproperties);
            }
            return _classesDictionary[type.Name];
        }

        #endregion

        #region Constructors

        public EfToAccess()
        {
            MConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                          AppDomain.CurrentDomain.BaseDirectory + "bin\\DataDoor.mdb";
            if (_classesDictionary == null) _classesDictionary = new Dictionary<string, PropertyInfo[]>();
            try
            {
                AccessConn = new OleDbConnection(MConnString);
                AccessCommand = new OleDbCommand();
                AccessCommand.Connection = AccessConn;
                //AccessConn.Open();
            }
            catch (Exception e)
            {
                // ReSharper disable once PossibleIntendedRethrow
                throw e;
            }
        }

        public EfToAccess(string connString)
        {
            MConnString = connString;
            if (_classesDictionary == null) _classesDictionary = new Dictionary<string, PropertyInfo[]>();
            try
            {
                AccessConn = new OleDbConnection(MConnString);
                AccessCommand = new OleDbCommand();
                AccessCommand.Connection = AccessConn;
                //AccessConn.Open();
            }
            catch (Exception e)
            {
                // ReSharper disable once PossibleIntendedRethrow
                throw e;
            }
        }

        #endregion

        #region Utilities

        private string GetWhereString(Expression func)
        {
            var conditionBuilder = new ConditionBuilder();
            conditionBuilder.Build(func);
            for (int i = 0; i < conditionBuilder.Arguments.Length; i++)
            {
                object ce = conditionBuilder.Arguments[i];
                if (ce == null)
                    conditionBuilder.Arguments[i] = "null";
                else if (ce is ValueType)
                    conditionBuilder.Arguments[i] = ce.ToString();
                else if (ce is string || ce is char)
                    conditionBuilder.Arguments[i] = string.Format("'{0}'", ce);
                else if (ce is DateTime)
                    conditionBuilder.Arguments[i] = string.Format("#{0}#", ce);
            }
            return string.Format(conditionBuilder.Condition, conditionBuilder.Arguments);
        }

        private void PropertyChangedEh(object sender, PropertyChangedEventArgs e)
        {
            //没有开启返回
            if (!MIsDetectionChange) return;
            //反射所有属性
            PropertyInfo[] properties = sender.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
            //查询主键
            PropertyInfo entityKey = GetEntityKeyFormAllProperty(properties);
            //没有主键的返回（表必须要有主键）
            if (entityKey == null) return;
            //表名字
            string table = sender.GetType().Name;
            //主键值
            string mainKey = entityKey.GetValue(sender, null).ToString();
            if (mainKey == "") return;
            //没有表 就添加
            if (!_mArrDetection.ContainsKey(table))
                _mArrDetection[table] = new Dictionary<string, Dictionary<string, object[]>>();
            //没有主键 就添加
            if (!_mArrDetection[table].ContainsKey(mainKey))
                _mArrDetection[table][mainKey] = new Dictionary<string, object[]>();
            //主键是不用更新的（一般数据库主键都是自动增长的吧，尤其快速开发的项目）
            if (e.PropertyName == mainKey) return;
            PropertyInfo p =
                properties.First(m => String.Compare(m.Name, e.PropertyName, StringComparison.OrdinalIgnoreCase) == 0);
            //赋值
            _mArrDetection[table][mainKey][e.PropertyName] = new[] {p.GetValue(sender, null), entityKey.Name};
        }

        private Action<object, object> LmdSet(Type entityType, string propName)
        {
            PropertyInfo p = entityType.GetProperty(propName);
            //对象实例
            ParameterExpression paramObj = Expression.Parameter(typeof (object), "obj");
            //值
            ParameterExpression paramVal = Expression.Parameter(typeof (object), "val");
            //转换参数为真实类型
            UnaryExpression bodyObj = Expression.Convert(paramObj, entityType);
            UnaryExpression bodyVal = Expression.Convert(paramVal, p.PropertyType);
            //调用给属性赋值的方法
            MethodCallExpression body = Expression.Call(bodyObj, p.GetSetMethod(), bodyVal);
            return Expression.Lambda<Action<object, object>>(body, paramObj, paramVal).Compile();
        }

        private IEnumerable<TEntity> SelectAll<TEntity>() where TEntity : new()
        {
            var TDefault = new TEntity();
            string entitySetName = TDefault.GetType().Name;
            string strSqlQuery = string.Format("SELECT * FROM {0}", entitySetName);
            MLastSqlCommand = strSqlQuery;
            return SelectWhere<TEntity>(strSqlQuery);
        }

        private int AddAll()
        {
            return _mArrAdd.Sum(o => AddCache(o));
        }

        private int DeleteAll()
        {
            int count=0;
            foreach (var kvp in _mArrDel)
            {
                string strDelWhere = "";
                List<string> arr = kvp.Value;
                for (int i = 0; i < arr.Count; i++)
                {
                    if (i == arr.Count - 1)
                        strDelWhere = strDelWhere + string.Format("{0}", arr[i]);
                    else
                        strDelWhere = strDelWhere + string.Format("{0} Or", arr[i]);
                }
                string strSqlQuery = string.Format("DELETE FROM {0} WHERE {1} ", kvp.Key, strDelWhere);
                MLastSqlCommand = strSqlQuery;
                var cmd = new OleDbCommand(strSqlQuery, AccessConn);
                count += cmd.ExecuteNonQuery();
            }
            return count;
        }

        private int UpdateAll()
        {
            return _mArrUpdate.Sum(o => UpdateCache(o));
        }

        private bool IsEntityKeyProperty(PropertyInfo info)
        {
            if (info.ToString() == "System.String Id") return true;
            if (info.ToString() == "System.String ExtId") return true;
            return Attribute.GetCustomAttributes(info).OfType<EdmScalarPropertyAttribute>().Any(key => key.EntityKeyProperty);
        }

        private PropertyInfo GetEntityKeyFormAllProperty(IEnumerable<PropertyInfo> properties)
        {
            return properties.FirstOrDefault(IsEntityKeyProperty);
        }

        #endregion

        #region Methods

        public void Dispose()
        {
        }

        /// <summary>
        /// DataTable 转换为List 集合
        /// </summary>
        /// <typeparam name="TResult">类型</typeparam>
        /// <param name="dt">DataTable</param>
        /// <returns></returns>
        public List<TResult> ToList<TResult>(DataTable dt) where TResult : class,new()
        {
            var prlist = new List<PropertyInfo>();
            Type t = typeof(TResult);
            Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });
            var oblist = new List<TResult>();
            foreach (DataRow row in dt.Rows)
            {
                var ob = new TResult();
                prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
                oblist.Add(ob);
            }
            return oblist;
        }
        /// <summary>
        /// List 集合转换为一个DataTable
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        ///// <param name="value"></param>
        /// <returns></returns>
        public DataTable ToDataTable<TResult>(IEnumerable<TResult> value) where TResult : class
        {
            var pList = new List<PropertyInfo>();
            Type type = typeof(TResult);
            var dt = new DataTable();
            Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });
            foreach (var item in value)
            {
                DataRow row = dt.NewRow();
                pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
                dt.Rows.Add(row);
            }
            return dt;
        }
        /// <summary>
        ///  //表名 //主键 //属性 值1 值2 主键类型名字
        /// </summary>
        /// <param name="destObject"></param>
        /// <param name="sourceObject"></param>
        public void CopyPropertiesFrom(object destObject, object sourceObject)
        {
            PropertyInfo[] destProperties = GetProperties(destObject.GetType());
            PropertyInfo[] sourceProperties = GetProperties(sourceObject.GetType());

            foreach (PropertyInfo p in destProperties)
            {
                if (p == null ) continue;
                var sourceProperty= sourceProperties.FirstOrDefault(m => m.Name == p.Name);
                if (sourceProperty == null) continue;
                object dstV = p.GetValue(destObject, null);
                object srcV = sourceProperty.GetValue(sourceObject, null);
                //if (srcV == null) continue;
                if (dstV != null && srcV != null) 
                if (dstV.ToString() == srcV.ToString()) continue;
                p.SetValue(destObject, srcV, null);
            }
        }

        public IEnumerable<TEntity> SelectWhere<TEntity>(string strSqlQuery) where TEntity : new()
        {
            if (AccessConn.State == ConnectionState.Closed) AccessConn.Open();
            var TDefault = new TEntity();
            var TBase = TDefault.GetType();
            PropertyInfo[] properties = GetProperties(TBase);

            while ((TBase.BaseType) != null)
            {
                if (TBase.Name == "EntityObject") break;
                TBase = TBase.BaseType;
            }
            bool isPceh = TBase != null && TBase.Name == "EntityObject";

            var records = new List<TEntity>();
            var cmd = new OleDbCommand(strSqlQuery, AccessConn);
            OleDbDataReader sqlReader = cmd.ExecuteReader();

            #region 数据库查询开始

            while (sqlReader != null && sqlReader.Read())
            {
                var value = new TEntity();
                //输入是EntityObject类型，那么加入属性改变事件
                if (isPceh)
                {
                    var eo = value as EntityObject;
                    if (eo != null) eo.PropertyChanged += PropertyChangedEh;
                }
                for (int i = 0; i < sqlReader.FieldCount; i++)
                {
                    string strField = sqlReader.GetName(i);
                    PropertyInfo p = properties.FirstOrDefault(m => m.Name == strField);
                    if (p==null) continue;
                   
                    switch (p.PropertyType.Name.ToLower())
                    {
                        case "int16":
                            if (!sqlReader.IsDBNull(i)) p.SetValue(value, sqlReader.GetInt16(i), null);
                            break;
                        case "int32":
                            if (!sqlReader.IsDBNull(i)) p.SetValue(value, sqlReader.GetInt32(i), null);
                            break;
                        case "int64":
                            if (!sqlReader.IsDBNull(i)) p.SetValue(value, sqlReader.GetInt64(i), null);
                            break;
                        case "string":
                            if (!sqlReader.IsDBNull(i)) p.SetValue(value, sqlReader.GetString(i), null);
                            break;
                        case "double":
                            if (!sqlReader.IsDBNull(i)) p.SetValue(value, sqlReader.GetDouble(i), null);
                            break;
                        case "float":
                            if (!sqlReader.IsDBNull(i)) p.SetValue(value, sqlReader.GetFloat(i), null);
                            break;
                        case "decimal":
                            if (!sqlReader.IsDBNull(i)) p.SetValue(value, sqlReader.GetDecimal(i), null);
                            break;
                        case "datetime":
                            if (!sqlReader.IsDBNull(i)) p.SetValue(value, sqlReader.GetDateTime(i), null);
                            break;
                        default:
                            if (!sqlReader.IsDBNull(i)) p.SetValue(value, sqlReader.GetValue(i), null);
                            break;
                    }
                }
                records.Add(value);
            }

            #endregion

            cmd.Clone();
            MIsDetectionChange = true;
            AccessConn.Close();
            return records;
        }

        public IEnumerable<TEntity> Where<TEntity>(Expression<Func<TEntity, bool>> func=null) where TEntity : new()
        {
            if (AccessConn.State == ConnectionState.Closed) AccessConn.Open(); ;
            var TDefault = new TEntity();
            string entitySetName = TDefault.GetType().Name;
            string strWhere = func == null ? "1=1" : GetWhereString(func).Replace("Where", entitySetName);
            string strSqlQuery = string.Format("SELECT * FROM {0} WHERE {1} ", entitySetName, strWhere);
            MLastSqlCommand = strSqlQuery;
            var data = SelectWhere<TEntity>(strSqlQuery);
            AccessConn.Close();
            return data;
        }

        public IEnumerable<TEntity> Where<TEntity>(string strWhere) where TEntity : new()
        {
            try
            {
                AccessConn.Open();
                var TDefault = new TEntity();
                string entitySetName = TDefault.GetType().Name;
                if (string.IsNullOrEmpty(strWhere)) strWhere = " 1 = 1 ";
                string strSqlQuery = string.Format("SELECT * FROM {0} WHERE {1} ", entitySetName, strWhere);
                MLastSqlCommand = strSqlQuery;
                var data = SelectWhere<TEntity>(strSqlQuery);
                AccessConn.Close();
                return data;
            }
            catch (Exception)
            {
                AccessConn.Close();
                return null;
            }
        }

        public IEnumerable<TEntity> FirstWhere<TEntity>(string strWhere) where TEntity : new()
        {
            try
            {
                AccessConn.Open();
                var TDefault = new TEntity();
                string entitySetName = TDefault.GetType().Name;
                if (string.IsNullOrEmpty(strWhere)) strWhere = " 1 = 1 ";
                string strSqlQuery = string.Format("SELECT top 1 * FROM {0} WHERE {1} ", entitySetName, strWhere);
                MLastSqlCommand = strSqlQuery;
                var data = SelectWhere<TEntity>(strSqlQuery);
                AccessConn.Close();
                return data;
            }
            catch (Exception)
            {
                AccessConn.Close();
                return null;
            }

        }

        public int Add<TEntity>(TEntity TDefault) where TEntity : class
        {
            if (AccessConn.State == ConnectionState.Closed) AccessConn.Open();
            var data = AddCache(TDefault); 
            AccessConn.Close();
            return data;
        }

        public int AddCache<TEntity>(TEntity TDefault) where TEntity : class
        {
            var type = TDefault.GetType();
            PropertyInfo[] properties = GetProperties(type);
            PropertyInfo entityKey = GetEntityKeyFormAllProperty(properties);
            if (entityKey == null) throw new Exception("未设置主键，无法使用本函数请使用其他函数!");
            string tabName = type.Name;
            string strRows = "";
            string strValues = "";

            #region Rows Values

            foreach (PropertyInfo info in properties)
            {
                object ce = info.GetValue(TDefault, null);
                string strLeft = info.Name;
                if (strLeft == "Money") strLeft = "[" + strLeft + "]";
                string strRight = "";
                if (info.Name == "Id" && ce == null) ce = Guid.NewGuid().ToString("D");
                if (info.Name == "ExtId" && ce == null) ce = Guid.NewGuid().ToString("D");
                if (ce == null) continue;
                if (ce is DateTime)
                    strRight = string.Format("#{0}#", ce);
                else if (ce is Decimal)
                    strRight = string.Format("{0}", ce);
                else if (ce is ValueType)
                    strRight = ce.ToString();
                else if (ce is string || ce is char)
                    strRight = string.Format("'{0}'", ce);
                //if (strLeft == EntityKey.Name)
                //{
                //    EntityValue = strRight;
                //    continue;
                //}
                if (strRight.Length == 0) continue;
                if (strLeft == "EntityKey" || strLeft == "EntityState") continue;
                strRows = strRows + strLeft + ",";
                strValues = strValues + strRight + ",";
            }

            #endregion

            if (strRows.Length < 2 || strValues.Length < 2) throw new Exception("SQL语句错误");
            strRows = strRows.Remove(strRows.Length - 1);
            strValues = strValues.Remove(strValues.Length - 1);
            string strSqlQuery = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tabName, strRows, strValues);
            MLastSqlCommand = strSqlQuery;
            var cmd = new OleDbCommand(strSqlQuery, AccessConn);
            var data = cmd.ExecuteNonQuery();
            return data;
        }

        public int Delete<TEntity>(TEntity TDefault)
        {
            if (AccessConn.State == ConnectionState.Closed) AccessConn.Open();
            var data = DeleteCache(TDefault);
            AccessConn.Close();
            return data;
        }

        public int DeleteCache<TEntity>(TEntity TDefault)
        {
            var type = TDefault.GetType();
            PropertyInfo[] properties = GetProperties(type);
            PropertyInfo entityKey = GetEntityKeyFormAllProperty(properties);
            if (entityKey == null) throw new Exception("未设置主键，无法使用本函数请使用其他函数!");
            string entityValue = entityKey.GetValue(TDefault, null).ToString();
            if (string.IsNullOrEmpty(entityValue)) throw new Exception("反射取值失败!");
            string entitySetName = type.Name;
            string strSqlQuery = string.Format("DELETE FROM {0} WHERE {1}='{2}' ", entitySetName, entityKey.Name, entityValue);
            MLastSqlCommand = strSqlQuery;
            var cmd = new OleDbCommand(strSqlQuery, AccessConn);
            var data = cmd.ExecuteNonQuery();
            return data;
        }

        public int Update<TEntity>(TEntity TDefault) where TEntity : class
        {
            if (AccessConn.State == ConnectionState.Closed) AccessConn.Open();
            var data = UpdateCache(TDefault);
            AccessConn.Close();
            return data;
        }

        public int UpdateCache<TEntity>(TEntity TDefault) where TEntity : class
        {
            var type = TDefault.GetType();
            PropertyInfo[] properties = GetProperties(type);
            PropertyInfo entityKey = GetEntityKeyFormAllProperty(properties);
            if (entityKey == null) throw new Exception("未设置主键，无法使用本函数请使用其他函数!");
            string entityValue = "";
            string strSet = "";

            #region Set

            foreach (PropertyInfo info in properties)
            {
                object ce = info.GetValue(TDefault, null);

                string strLeft = info.Name;
                if (strLeft == "Money") strLeft = "[" + strLeft + "]";
                string strRight = "";
                if (ce == null) continue;
                if (ce is DateTime){
                    dynamic dt= DateTime.Parse(ce.ToString());
                    strRight = string.Format("#{0}#", dt.ToString("yyyy-MM-dd hh:mm:ss"));
                }
                else if (ce is ValueType)
                    strRight = ce.ToString();
                else if (ce is string || ce is char)
                    strRight = string.Format("'{0}'", ce);
                if (strLeft == entityKey.Name)
                {
                    entityValue = strRight;
                    continue;
                }
                if (strRight.Length == 0) continue;
                if (strLeft == "EntityKey" || strLeft == "EntityState") continue;
                strSet += strLeft + "=" + strRight + ",";
            }
            if (strSet.Length < 2) throw new Exception("SQL语句错误");
            strSet = strSet.Remove(strSet.Length - 1);

            #endregion

            string tabName = TDefault.GetType().Name;
            string strSqlQuery = string.Format("UPDATE {0} SET {1} WHERE {2}={3} ", tabName, strSet, entityKey.Name,
                entityValue);
            MLastSqlCommand = strSqlQuery;
            var cmd = new OleDbCommand(strSqlQuery, AccessConn);
            var data = cmd.ExecuteNonQuery();
            return data;
        }

        public int AutoUpdate()
        {
            var arrSqlText = new List<string>();
            foreach (var tabKvp in _mArrDetection)
            {
                //遍历表名
                string tabName = tabKvp.Key;
                foreach (var keyKvp in tabKvp.Value)
                {
                    string strSet = "";
                    string strMainKeyName = "";

                    #region 把数据列出来例如： a=1,c="2"

                    foreach (var valueKvp in keyKvp.Value)
                    {
                        if (strMainKeyName.Length == 0) strMainKeyName = valueKvp.Value[1].ToString();
                        object va = valueKvp.Value[0];
                        string strLeft = valueKvp.Key;
                        string strRight = "";

                        #region 根据值确认是否添加引号

                        if (valueKvp.Value == null) continue;
                        if (va is DateTime)
                            strRight = string.Format("#{0}#", va);
                        else if (va is ValueType)
                            strRight = va.ToString();
                        else if (va is string || va is char)
                            strRight = string.Format("'{0}'", va);
                        #endregion

                        if (strRight.Length == 0) continue;
                        if (strLeft == "EntityKey" || strLeft == "EntityState") continue;
                        strSet += strLeft + "=" + strRight + ",";
                    }

                    #endregion

                    if (strSet.Length < 2) continue;
                    strSet = strSet.Remove(strSet.Length - 1);
                    //根据当前的主键[ID] 生成一个SQL语句
                    string strSqlQuery = string.Format("UPDATE {0} SET {1} WHERE {2}={3} ", tabName, strSet,
                        strMainKeyName, keyKvp.Key);
                    arrSqlText.Add(strSqlQuery);
                }
            }
            foreach (string strSqlQuery in arrSqlText)
            {
                MLastSqlCommand = strSqlQuery;
                try
                {
                    var cmd = new OleDbCommand(strSqlQuery, AccessConn);
                    cmd.ExecuteNonQuery();
                }
                // ReSharper disable once EmptyGeneralCatchClause
                catch (Exception)
                {
                }
            }
            return 0;
        }

        public IEnumerable<TEntity> CreateObjectSet<TEntity>(string entitySetName) where TEntity : new()
        {
            return SelectAll<TEntity>();
        }

        public void AddObject(object o)
        {
            _mArrAdd.Add(o);
        }

        public void AddObjects(IEnumerable<object> os)
        {
            _mArrAdd.AddRange(os);
        }

        public void DeleteObject(object TDefault)
        {
            var type = TDefault.GetType();
            PropertyInfo[] properties = GetProperties(type);
            PropertyInfo entityKey = GetEntityKeyFormAllProperty(properties);
            if (entityKey == null) throw new Exception("未设置主键，无法使用本函数请使用其他函数!");
            string entityValue = entityKey.GetValue(TDefault, null).ToString();
            if (string.IsNullOrEmpty(entityValue)) throw new Exception("反射取值失败!");
            string keyName = type.Name;
            if (!_mArrDel.ContainsKey(keyName)) _mArrDel.Add(keyName, new List<string>());
            _mArrDel[keyName].Add(string.Format("({0}='{1}')", entityKey.Name, entityValue));
        }

        public void DeleteObjects(IEnumerable<object> os)
        {
            foreach (object o in os)
            {
                DeleteObject(o);
            }
        }

        public void UpdateObject(object o)
        {
            _mArrUpdate.Add(o);
        }

        public void UpdateObjects(IEnumerable<object> os)
        {
            _mArrUpdate.AddRange(os);
        }

        public int SaveChanges()
        {
            if (AccessConn.State != ConnectionState.Closed) AccessConn.Close();
            AccessConn.Open();
            int i = 0;
            if (_mArrDel.Count > 0)
            {
                i = i+DeleteAll();
                _mArrDel.Clear();
            }
            if (_mArrAdd.Count > 0)
            {
                i = i + AddAll();
                _mArrAdd.Clear();
            }
            if (_mArrUpdate.Count > 0)
            {
                i = i + UpdateAll();
                _mArrUpdate.Clear();
            }
            if (_mArrDetection.Count > 0)
            {
                i = i + AutoUpdate();
                _mArrDetection.Clear();
            }
            MIsDetectionChange = false;
            AccessConn.Close();
            return i;
        }

        public string GetCountNo(string tableName,Boolean isDateTime=false,string where="",int lenght=6)
        {
            var myDataSet = new DataSet();
            //if (string.IsNullOrEmpty(where)) 
            where = " where Type='" + tableName + "'" + where;
            string sql="select Count(*) As CountNo from T_No " +where;
            var myaccessdp = new OleDbDataAdapter
            {
                SelectCommand = new OleDbCommand(sql, AccessConn)
            };
            myaccessdp.Fill(myDataSet);
            int count = Convert.ToInt16(myDataSet.Tables[0].Rows[0][0].ToString()) + 1;
            var data = count.ToString();
            data = data.PadLeft(lenght, '0');
            if (isDateTime) data = DateTime.Now.ToString("yyyymmddhhmmss")+data;
            return data;
        }

        public Boolean SetCountNo(string tableName,string no)
        {
            var tNo=new T_No
            {
                Type = tableName, ShopId = S_Admin.ShopId,NoStr =no,AddTime = DateTime.Now
            };

            return Add(tNo) >= 1;
        }

        /// <summary>
        ///     RunSQLcmd 用于运行SQL语句的方法
        ///     返回值为一个bool
        /// </summary>
        /// <param name="cmdStr">要运行的 SQL 语句</param>
        /// <returns>返回值为 bool 类型 </returns>
        public bool RunSqLcmd(string cmdStr)
        {
            try
            {
                AccessConn.Open();
                var cmd1 = new OleDbCommand("", AccessConn) {CommandText = cmdStr};
                AccessCommand = cmd1;
                cmd1.ExecuteNonQuery(); //运行SQL语句
                AccessConn.Close();
                return true;
            }
            catch
            {
                AccessConn.Close();
                return false;
            }
        }

        /// <summary>
        ///     RunSQLcmdDataSet 用于运行SQL语句的方法
        ///     返回值为一个DataSet
        ///     注意此方法与 RunSQLcmd 的不同
        /// </summary>
        /// <param name="cmdStr">要运行的 SQL 语句</param>
        /// <returns>返回值为 DataSet 类型 </returns>
        public DataSet RunSqLcmdDataSet(string cmdStr)
        {
            try
            {
                //cmdStr = cmdStr.Replace("%", "*");
                var myDataSet = new DataSet();
                var myaccessdp = new OleDbDataAdapter
                {
                    SelectCommand = new OleDbCommand(cmdStr, AccessConn)
                };
                myaccessdp.Fill(myDataSet);
                return myDataSet;
            }
            catch (Exception)
            {
                // Msgbox._Show(Ex.Message,FireTiger_ControlLibrary.FireTiger_Dialog.MsgBoxStyle.Information,false,"在runsqlcmd的方法中出错");
                //System.Windows.Forms.MessageBox.Show(Ex.Message,"在runsqlcmd的方法中出错");
                return new DataSet();
            }
        }

        public void BeginTran()
        {
            if (AccessConn.State == ConnectionState.Closed) AccessConn.Open();
            AccessCommand.Transaction = AccessConn.BeginTransaction();
        }
        public void Rollback()
        {
            AccessCommand.Transaction.Rollback();
            AccessConn.Close();
        }
        public void Commit()
        {
            AccessCommand.Transaction.Commit();
            AccessConn.Close();
        }
        public int Execute(string sql)
        {
            if (AccessConn.State == ConnectionState.Closed) AccessConn.Open();
            AccessCommand.CommandText = sql;
            return AccessCommand.ExecuteNonQuery(); //运行SQL语句
        }
        #endregion
    }

    public abstract class ExpressionVisitor35
    {
        protected virtual Expression Visit(Expression exp)
        {
            if (exp == null) return null;
            switch (exp.NodeType)
            {
                case ExpressionType.Negate:
                case ExpressionType.NegateChecked:
                case ExpressionType.Not:
                case ExpressionType.Convert:
                case ExpressionType.ConvertChecked:
                case ExpressionType.ArrayLength:
                case ExpressionType.Quote:
                case ExpressionType.TypeAs:
                    return VisitUnary((UnaryExpression) exp);
                case ExpressionType.Add:
                case ExpressionType.AddChecked:
                case ExpressionType.Subtract:
                case ExpressionType.SubtractChecked:
                case ExpressionType.Multiply:
                case ExpressionType.MultiplyChecked:
                case ExpressionType.Divide:
                case ExpressionType.Modulo:
                case ExpressionType.And:
                case ExpressionType.AndAlso:
                case ExpressionType.Or:
                case ExpressionType.OrElse:
                case ExpressionType.LessThan:
                case ExpressionType.LessThanOrEqual:
                case ExpressionType.GreaterThan:
                case ExpressionType.GreaterThanOrEqual:
                case ExpressionType.Equal:
                case ExpressionType.NotEqual:
                case ExpressionType.Coalesce:
                case ExpressionType.ArrayIndex:
                case ExpressionType.RightShift:
                case ExpressionType.LeftShift:
                case ExpressionType.ExclusiveOr:
                    return VisitBinary((BinaryExpression) exp);
                case ExpressionType.TypeIs:
                    return VisitTypeIs((TypeBinaryExpression) exp);
                case ExpressionType.Conditional:
                    return VisitConditional((ConditionalExpression) exp);
                case ExpressionType.Constant:
                    return VisitConstant((ConstantExpression) exp);
                case ExpressionType.Parameter:
                    return VisitParameter((ParameterExpression) exp);
                case ExpressionType.MemberAccess:
                    return VisitMemberAccess((MemberExpression) exp);
                case ExpressionType.Call:
                    return VisitMethodCall((MethodCallExpression) exp);
                case ExpressionType.Lambda:
                    return VisitLambda((LambdaExpression) exp);
                case ExpressionType.New:
                    return VisitNew((NewExpression) exp);
                case ExpressionType.NewArrayInit:
                case ExpressionType.NewArrayBounds:
                    return VisitNewArray((NewArrayExpression) exp);
                case ExpressionType.Invoke:
                    return VisitInvocation((InvocationExpression) exp);
                case ExpressionType.MemberInit:
                    return VisitMemberInit((MemberInitExpression) exp);
                case ExpressionType.ListInit:
                    return VisitListInit((ListInitExpression) exp);
                default:
                    throw new Exception(string.Format("Unhandled expression type: '{0}'", exp.NodeType));
            }
        }

        protected virtual MemberBinding VisitBinding(MemberBinding binding)
        {
            switch (binding.BindingType)
            {
                case MemberBindingType.Assignment:
                    return VisitMemberAssignment((MemberAssignment) binding);
                case MemberBindingType.MemberBinding:
                    return VisitMemberMemberBinding((MemberMemberBinding) binding);
                case MemberBindingType.ListBinding:
                    return VisitMemberListBinding((MemberListBinding) binding);
                default:
                    throw new Exception(string.Format("Unhandled binding type '{0}'", binding.BindingType));
            }
        }

        protected virtual ElementInit VisitElementInitializer(ElementInit initializer)
        {
            ReadOnlyCollection<Expression> arguments = VisitExpressionList(initializer.Arguments);
            if (arguments != initializer.Arguments)
            {
                return Expression.ElementInit(initializer.AddMethod, arguments);
            }
            return initializer;
        }

        protected virtual Expression VisitUnary(UnaryExpression u)
        {
            Expression operand = Visit(u.Operand);
            if (operand != u.Operand)
            {
                return Expression.MakeUnary(u.NodeType, operand, u.Type, u.Method);
            }
            return u;
        }

        protected virtual Expression VisitBinary(BinaryExpression b)
        {
            Expression left = Visit(b.Left);
            Expression right = Visit(b.Right);
            Expression conversion = Visit(b.Conversion);
            if (left != b.Left || right != b.Right || conversion != b.Conversion)
            {
                if (b.NodeType == ExpressionType.Coalesce && b.Conversion != null)
                    return Expression.Coalesce(left, right, conversion as LambdaExpression);
                return Expression.MakeBinary(b.NodeType, left, right, b.IsLiftedToNull, b.Method);
            }
            return b;
        }

        protected virtual Expression VisitTypeIs(TypeBinaryExpression b)
        {
            Expression expr = Visit(b.Expression);
            if (expr != b.Expression)
            {
                return Expression.TypeIs(expr, b.TypeOperand);
            }
            return b;
        }

        protected virtual Expression VisitConstant(ConstantExpression c)
        {
            return c;
        }

        protected virtual Expression VisitConditional(ConditionalExpression c)
        {
            Expression test = Visit(c.Test);
            Expression ifTrue = Visit(c.IfTrue);
            Expression ifFalse = Visit(c.IfFalse);
            if (test != c.Test || ifTrue != c.IfTrue || ifFalse != c.IfFalse)
            {
                return Expression.Condition(test, ifTrue, ifFalse);
            }
            return c;
        }

        protected virtual Expression VisitParameter(ParameterExpression p)
        {
            return p;
        }

        protected virtual Expression VisitMemberAccess(MemberExpression m)
        {
            Expression exp = Visit(m.Expression);
            if (exp != m.Expression)
            {
                return Expression.MakeMemberAccess(exp, m.Member);
            }
            return m;
        }

        protected virtual Expression VisitMethodCall(MethodCallExpression m)
        {
            //MethodCallExpression mce = m;
            //if (mce.Method.Name == "Contains")
            //    return string.Format("({0} like {1})", ConditionBuilder.ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
            //else if (mce.Method.Name == "NotLike")
            //    return string.Format("({0} Not like {1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
            //else if (mce.Method.Name == "In")
            //    return string.Format("{0} In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
            //else if (mce.Method.Name == "NotIn")
            //    return string.Format("{0} Not In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));


            Expression obj = Visit(m.Object);
            IEnumerable<Expression> args = VisitExpressionList(m.Arguments);
            if (obj != m.Object || !Equals(args, m.Arguments))
            {
                return Expression.Call(obj, m.Method, args);
            }
            return m;
        }

        protected virtual ReadOnlyCollection<Expression> VisitExpressionList(ReadOnlyCollection<Expression> original)
        {
            List<Expression> list = null;
            for (int i = 0, n = original.Count; i < n; i++)
            {
                Expression p = Visit(original[i]);
                if (list != null)
                {
                    list.Add(p);
                }
                else if (p != original[i])
                {
                    list = new List<Expression>(n);
                    for (int j = 0; j < i; j++)
                    {
                        list.Add(original[j]);
                    }
                    list.Add(p);
                }
            }
            if (list != null)
            {
                return list.AsReadOnly();
            }
            return original;
        }

        protected virtual MemberAssignment VisitMemberAssignment(MemberAssignment assignment)
        {
            Expression e = Visit(assignment.Expression);
            if (e != assignment.Expression)
            {
                return Expression.Bind(assignment.Member, e);
            }
            return assignment;
        }

        protected virtual MemberMemberBinding VisitMemberMemberBinding(MemberMemberBinding binding)
        {
            IEnumerable<MemberBinding> bindings = VisitBindingList(binding.Bindings);
            if (!Equals(bindings, binding.Bindings))
            {
                return Expression.MemberBind(binding.Member, bindings);
            }
            return binding;
        }

        protected virtual MemberListBinding VisitMemberListBinding(MemberListBinding binding)
        {
            IEnumerable<ElementInit> initializers = VisitElementInitializerList(binding.Initializers);
            if (!Equals(initializers, binding.Initializers))
            {
                return Expression.ListBind(binding.Member, initializers);
            }
            return binding;
        }

        protected virtual IEnumerable<MemberBinding> VisitBindingList(ReadOnlyCollection<MemberBinding> original)
        {
            List<MemberBinding> list = null;
            for (int i = 0, n = original.Count; i < n; i++)
            {
                MemberBinding b = VisitBinding(original[i]);
                if (list != null)
                {
                    list.Add(b);
                }
                else if (b != original[i])
                {
                    list = new List<MemberBinding>(n);
                    for (int j = 0; j < i; j++)
                    {
                        list.Add(original[j]);
                    }
                    list.Add(b);
                }
            }
            if (list != null)
                return list;
            return original;
        }

        protected virtual IEnumerable<ElementInit> VisitElementInitializerList(ReadOnlyCollection<ElementInit> original)
        {
            List<ElementInit> list = null;
            for (int i = 0, n = original.Count; i < n; i++)
            {
                ElementInit init = VisitElementInitializer(original[i]);
                if (list != null)
                {
                    list.Add(init);
                }
                else if (init != original[i])
                {
                    list = new List<ElementInit>(n);
                    for (int j = 0; j < i; j++)
                    {
                        list.Add(original[j]);
                    }
                    list.Add(init);
                }
            }
            if (list != null)
                return list;
            return original;
        }

        protected virtual Expression VisitLambda(LambdaExpression lambda)
        {
            Expression body = Visit(lambda.Body);
            if (body != lambda.Body)
            {
                return Expression.Lambda(lambda.Type, body, lambda.Parameters);
            }
            return lambda;
        }

        protected virtual NewExpression VisitNew(NewExpression nex)
        {
            IEnumerable<Expression> args = VisitExpressionList(nex.Arguments);
            if (!Equals(args, nex.Arguments))
            {
                return Expression.New(nex.Constructor, args, nex.Members);
            }
            return nex;
        }

        protected virtual Expression VisitMemberInit(MemberInitExpression init)
        {
            NewExpression n = VisitNew(init.NewExpression);
            IEnumerable<MemberBinding> bindings = VisitBindingList(init.Bindings);
            if (n != init.NewExpression || !Equals(bindings, init.Bindings))
            {
                return Expression.MemberInit(n, bindings);
            }
            return init;
        }

        protected virtual Expression VisitListInit(ListInitExpression init)
        {
            NewExpression n = VisitNew(init.NewExpression);
            IEnumerable<ElementInit> initializers = VisitElementInitializerList(init.Initializers);
            if (n != init.NewExpression || !Equals(initializers, init.Initializers))
            {
                return Expression.ListInit(n, initializers);
            }
            return init;
        }

        protected virtual Expression VisitNewArray(NewArrayExpression na)
        {
            IEnumerable<Expression> exprs = VisitExpressionList(na.Expressions);
            if (!Equals(exprs, na.Expressions))
            {
                if (na.NodeType == ExpressionType.NewArrayInit)
                {
                    return Expression.NewArrayInit(na.Type.GetElementType(), exprs);
                }
                return Expression.NewArrayBounds(na.Type.GetElementType(), exprs);
            }
            return na;
        }

        protected virtual Expression VisitInvocation(InvocationExpression iv)
        {
            IEnumerable<Expression> args = VisitExpressionList(iv.Arguments);
            Expression expr = Visit(iv.Expression);
            if (!Equals(args, iv.Arguments) || expr != iv.Expression)
            {
                return Expression.Invoke(expr, args);
            }
            return iv;
        }
    }

    public class PartialEvaluator : ExpressionVisitor35
    {
        private HashSet<Expression> _mCandidates;
        private readonly Func<Expression, bool> _mFnCanBeEvaluated;

        public PartialEvaluator()
            : this(CanBeEvaluatedLocally)
        {
        }

        public PartialEvaluator(Func<Expression, bool> fnCanBeEvaluated)
        {
            _mFnCanBeEvaluated = fnCanBeEvaluated;
        }

        public Expression Eval(Expression exp)
        {
            _mCandidates = new Nominator(_mFnCanBeEvaluated).Nominate(exp);

            return Visit(exp);
        }

        protected override Expression Visit(Expression exp)
        {
            if (exp == null)
            {
                return null;
            }

            if (_mCandidates.Contains(exp))
            {
                return Evaluate(exp);
            }

            return base.Visit(exp);
        }

        private Expression Evaluate(Expression e)
        {
            if (e.NodeType == ExpressionType.Constant)
            {
                return e;
            }

            LambdaExpression lambda = Expression.Lambda(e);
            Delegate fn = lambda.Compile();
            return Expression.Constant(fn.DynamicInvoke(null), e.Type);
        }

        private static bool CanBeEvaluatedLocally(Expression exp)
        {
            return exp.NodeType != ExpressionType.Parameter;
        }

        #region Nominator

        /// <summary>
        ///     Performs bottom-up analysis to determine which nodes can possibly
        ///     be part of an evaluated sub-tree.
        /// </summary>
        private class Nominator : ExpressionVisitor35
        {
            private HashSet<Expression> m_candidates;
            private bool _mCannotBeEvaluated;
            private readonly Func<Expression, bool> m_fnCanBeEvaluated;

            internal Nominator(Func<Expression, bool> fnCanBeEvaluated)
            {
                m_fnCanBeEvaluated = fnCanBeEvaluated;
            }

            internal HashSet<Expression> Nominate(Expression expression)
            {
                m_candidates = new HashSet<Expression>();
                Visit(expression);
                return m_candidates;
            }

            protected override Expression Visit(Expression expression)
            {
                if (expression != null)
                {
                    bool saveCannotBeEvaluated = _mCannotBeEvaluated;
                    _mCannotBeEvaluated = false;

                    base.Visit(expression);

                    if (!_mCannotBeEvaluated)
                    {
                        if (m_fnCanBeEvaluated(expression))
                        {
                            m_candidates.Add(expression);
                        }
                        else
                        {
                            _mCannotBeEvaluated = true;
                        }
                    }

                    _mCannotBeEvaluated |= saveCannotBeEvaluated;
                }

                return expression;
            }
        }

        #endregion
    }

    internal class ConditionBuilder : ExpressionVisitor35
    {
        private List<object> _mArguments;
        private Stack<string> _mConditionParts;

        public string Condition { get; private set; }

        public object[] Arguments { get; private set; }

        public void Build(Expression expression)
        {
            var evaluator = new PartialEvaluator();
            Expression evaluatedExpression = evaluator.Eval(expression);

            _mArguments = new List<object>();
            _mConditionParts = new Stack<string>();

            Visit(evaluatedExpression);

            Arguments = _mArguments.ToArray();
            Condition = _mConditionParts.Count > 0 ? _mConditionParts.Pop() : null;
        }

        protected override Expression VisitBinary(BinaryExpression b)
        {
            if (b == null) return null;

            string opr;
            switch (b.NodeType)
            {
                case ExpressionType.Call:
                    opr = "like";
                    break;
                case ExpressionType.Equal:
                    opr = "=";
                    break;
                case ExpressionType.NotEqual:
                    opr = "<>";
                    break;
                case ExpressionType.GreaterThan:
                    opr = ">";
                    break;
                case ExpressionType.GreaterThanOrEqual:
                    opr = ">=";
                    break;
                case ExpressionType.LessThan:
                    opr = "<";
                    break;
                case ExpressionType.LessThanOrEqual:
                    opr = "<=";
                    break;
                case ExpressionType.AndAlso:
                    opr = "AND";
                    break;
                case ExpressionType.OrElse:
                    opr = "OR";
                    break;
                case ExpressionType.Add:
                    opr = "+";
                    break;
                case ExpressionType.Subtract:
                    opr = "-";
                    break;
                case ExpressionType.Multiply:
                    opr = "*";
                    break;
                case ExpressionType.Divide:
                    opr = "/";
                    break;
                default:
                    throw new NotSupportedException(b.NodeType + "is not supported.");
            }

            Visit(b.Left);
            Visit(b.Right);

            string right = _mConditionParts.Pop();
            string left = _mConditionParts.Pop();

            string condition = String.Format("({0} {1} {2})", left, opr, right);
            _mConditionParts.Push(condition);

            return b;
        }

        protected override Expression VisitConstant(ConstantExpression c)
        {
            if (c == null) return null;

            _mArguments.Add(c.Value);
            _mConditionParts.Push(String.Format("{{{0}}}", _mArguments.Count - 1));

            return c;
        }

        protected override Expression VisitMemberAccess(MemberExpression m)
        {
            if (m == null) return null;

            var propertyInfo = m.Member as PropertyInfo;
            if (propertyInfo == null) return m;

            _mConditionParts.Push(String.Format("(Where.{0})", propertyInfo.Name));

            return m;
        }

        #region 其他人的

        private static string BinarExpressionProvider(Expression left, Expression right, ExpressionType type)
        {
            string sb = "(";
            //先处理左边
            sb += ExpressionRouter(left);

            sb += ExpressionTypeCast(type);

            //再处理右边
            string tmpStr = ExpressionRouter(right);

            if (tmpStr == "null")
            {
                if (sb.EndsWith(" ="))
                    sb = sb.Substring(0, sb.Length - 1) + " is null";
                else if (sb.EndsWith("<>"))
                    sb = sb.Substring(0, sb.Length - 1) + " is not null";
            }
            else
                sb += tmpStr;
            return sb + ")";
        }

        private static string ExpressionRouter(Expression exp)
        {
            string sb = string.Empty;
            if (exp == null) return sb;
            // ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
            if (exp is BinaryExpression)
            {
                var be = ((BinaryExpression) exp);
                return BinarExpressionProvider(be.Left, be.Right, be.NodeType);
            }
            // ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
            if (exp is MemberExpression)
            {
                var me = ((MemberExpression) exp);
                return me.Member.Name;
            }
            // ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
            if (exp is NewArrayExpression)
            {
                var ae = ((NewArrayExpression) exp);
                var tmpstr = new StringBuilder();
                foreach (Expression ex in ae.Expressions)
                {
                    tmpstr.Append(ExpressionRouter(ex));
                    tmpstr.Append(",");
                }
                return tmpstr.ToString(0, tmpstr.Length - 1);
            }
            // ReSharper disable once CanBeReplacedWithTryCastAndCheckForNull
            if (exp is MethodCallExpression)
            {
                var mce = (MethodCallExpression) exp;
                if (mce.Method.Name == "Like")
                    return string.Format("({0} like {1})", ExpressionRouter(mce.Arguments[0]),
                        ExpressionRouter(mce.Arguments[1]));
                if (mce.Method.Name == "NotLike")
                    return string.Format("({0} Not like {1})", ExpressionRouter(mce.Arguments[0]),
                        ExpressionRouter(mce.Arguments[1]));
                if (mce.Method.Name == "In")
                    return string.Format("{0} In ({1})", ExpressionRouter(mce.Arguments[0]),
                        ExpressionRouter(mce.Arguments[1]));
                if (mce.Method.Name == "NotIn")
                    return string.Format("{0} Not In ({1})", ExpressionRouter(mce.Arguments[0]),
                        ExpressionRouter(mce.Arguments[1]));
            }
            else if (exp is ConstantExpression)
            {
                var ce = ((ConstantExpression) exp);
                if (ce.Value == null)
                    return "null";
                if (ce.Value is ValueType)
                    return ce.Value.ToString();
                if (ce.Value is string || ce.Value is DateTime || ce.Value is char)
                    return string.Format("'{0}'", ce.Value);
            }
            else if (exp is UnaryExpression)
            {
                var ue = ((UnaryExpression) exp);
                return ExpressionRouter(ue.Operand);
            }
            return null;
        }

        private static string ExpressionTypeCast(ExpressionType type)
        {
            switch (type)
            {
                case ExpressionType.And:
                case ExpressionType.AndAlso:
                    return " AND ";
                case ExpressionType.Equal:
                    return " =";
                case ExpressionType.GreaterThan:
                    return " >";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.LessThanOrEqual:
                    return "<=";
                case ExpressionType.NotEqual:
                    return "<>";
                case ExpressionType.Or:
                case ExpressionType.OrElse:
                    return " Or ";
                case ExpressionType.Add:
                case ExpressionType.AddChecked:
                    return "+";
                case ExpressionType.Subtract:
                case ExpressionType.SubtractChecked:
                    return "-";
                case ExpressionType.Divide:
                    return "/";
                case ExpressionType.Multiply:
                case ExpressionType.MultiplyChecked:
                    return "*";
                default:
                    return null;
            }
        }

        #endregion
    }
}